package com.kingdee.eas.custom.wg.reports.app;

import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.bos.dao.IObjectValue;
import com.kingdee.bos.framework.ejb.EJBFactory;
import com.kingdee.bos.metadata.entity.*;
import com.kingdee.bos.metadata.query.util.CompareType;
import com.kingdee.bos.util.BOSUuid;
import com.kingdee.eas.basedata.master.material.MaterialGroupInfo;
import com.kingdee.eas.basedata.master.material.MaterialGroupStandardInfo;
import com.kingdee.eas.basedata.org.FullOrgUnitInfo;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.framework.report.util.*;
import com.kingdee.eas.scm.common.util.SCMUtils;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

public class StockTransferRptFacadeControllerBean extends AbstractStockTransferRptFacadeControllerBean
{

    public StockTransferRptFacadeControllerBean()
    {
    }
    
    protected RptParams _findData(Context ctx, RptParams param, int start, int count, String tempTableName, Object treeTypeObject)
        throws BOSException, EASBizException
    {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT COUNT(*) FROM ");
        sql.append(param.getString("tempTable"));
        sql.append(" WHERE FMaterialID = '");
        sql.append(param.getString("FMaterialID"));
        sql.append("' AND FLevel = ");
        sql.append(param.getInt("FLevel"));
        RptRowSet rs = executeQuery(sql.toString(), null, ctx);
        rs.next();
        int cout = rs.getInt(0);
        sql = new StringBuffer();
        sql.append("SELECT FMaterialNum, FMaterialName, material.FShortName,FMaterialModel, FIssueStorageName, FReceiptStorageName,");
        sql.append("FBaseUnitName,FStatUnitName,isnull(FOriginalQty,0) as FOriginalQty ,FAllocateQty,FIssueBaseQty,FReceiptBaseQty,FOnWayBaseQty,FDamageBaseQty,");
        sql.append("FIssueAmt,FExportAmt,FImportAmt,FExportAmt-FImportAmt AS FOnWayAmt,FDamageAmt,FIndex, FMaterialID,FIssueOrgID,FReceiptOrgID ,FQtyPrecision,");
        sql.append("FLevel,FIsLeaf,FIssueStorageName AS FIssueStorageName2 ");
        sql.append("FROM ");
        sql.append(param.getString("tempTable"));
        sql.append(" AS temp ");
        sql.append(" LEFT OUTER JOIN T_BD_Material as material on temp.FMaterialNum = material.FNumber ");
        sql.append(" WHERE FMaterialID = '");
        sql.append(param.getString("FMaterialID"));
        sql.append("' AND FParentID = '");
        sql.append(param.getString("FParentID"));
        sql.append("' Order By FMaterialNum, FIssueStorageName, FIndex");
        if(start <= -1 && count <= -1)
            rs = executeQuery(sql.toString(), null, ctx);
        else
            rs = executeQuery(sql.toString(), null, start, count, ctx);
        param.setObject("rowset", rs);
        param.setInt("LevelCount", cout);
        return param;
    }

    protected RptParams _createTempTable(Context ctx, RptParams params)
        throws BOSException, EASBizException
    {
        dropTable(params.getString("tempTable"), ctx);
        String tempTable = getTempTableName("StockTransferRpt", ctx);
        String tempOrgTable = getTempTableName("StockTransferRptOrg", ctx);
        String aa = "";
        String sql = getCreateTempTab(tempTable);
        executeSQL(ctx, sql);
        Boolean ckBxByStatUnit = (Boolean)params.getObject("ckBxByStatUnit");
        createOrgTempTable(tempOrgTable, params, ctx);
        if(params.getBoolean("kDRadioAll") || params.getBoolean("kDRadioStock"))
        {
            sql = getInsertSqlForStockTransferBill(tempTable, tempOrgTable, params, ctx);
            executeSQL(ctx, sql);
        }
        if(params.getBoolean("kDRadioAll") || params.getBoolean("kDRadioOrder"))
        {
            sql = getInsertSqlForTransferOrderBill(tempTable, tempOrgTable, params, ctx);
            executeSQL(ctx, sql);
        }
        if(ckBxByStatUnit.booleanValue())
            updateStatUnit(ctx, tempTable, params);
        executeTotal(tempTable, tempOrgTable, ctx, params);
        sql = getUpdateNullSql(tempTable);
        executeSQL(ctx, sql);
        String countSql = "select count(1) cc from " + tempTable;
        Boolean ckBxShowMShortName = (Boolean)params.getObject("ckBxShowMShortName");
        StringBuffer sqlWhc = new StringBuffer();
        sqlWhc.append(" AS temp ").append(" inner  JOIN T_BD_Material as material on temp.FMaterialNum = material.FNumber ");
        sqlWhc.append(" where flevel = 1 ");
        if(ckBxShowMShortName != null && ckBxShowMShortName.booleanValue())
        {
            String txtMShortNameFrom = (String)params.getObject("shortNameFrom");
            String txtMShortNameTo = (String)params.getObject("shortNameTo");
            if(txtMShortNameFrom == null || txtMShortNameFrom.trim().length() == 0)
                txtMShortNameFrom = null;
            if(txtMShortNameTo == null || txtMShortNameTo.trim().length() == 0)
                txtMShortNameTo = null;
            if(txtMShortNameFrom != null && txtMShortNameFrom.trim().length() > 0)
            {
                if(txtMShortNameFrom.indexOf("'") != -1)
                    txtMShortNameFrom = txtMShortNameFrom.replaceAll("'", "''");
                sqlWhc.append(" and material.FShortName >= '").append(txtMShortNameFrom.trim()).append("' ");
            }
            if(txtMShortNameFrom == null && txtMShortNameTo != null && txtMShortNameTo.trim().length() > 0)
            {
                if(txtMShortNameTo.indexOf("'") != -1)
                    txtMShortNameTo = txtMShortNameTo.replaceAll("'", "''");
                sqlWhc.append(" and ( material.FShortName <= '").append(txtMShortNameTo.trim()).append("' or material.FShortName is null ) ");
            }
            if(txtMShortNameFrom != null && txtMShortNameTo != null && txtMShortNameTo.trim().length() > 0)
            {
                if(txtMShortNameTo.indexOf("'") != -1)
                    txtMShortNameTo = txtMShortNameTo.replaceAll("'", "''");
                sqlWhc.append(" and material.FShortName <= '").append(txtMShortNameTo.trim()).append("' ");
            }
        }
        countSql = countSql + sqlWhc.toString();
        RptRowSet rs = executeQuery(countSql, null, ctx);
        rs.next();
        int count = rs.getInt(0);
        RptParams result = new RptParams();
        result.setString("tempTable", tempTable);
        result.setInt("verticalCount", count);
        dropTable(tempOrgTable, ctx);
        return result;
    }

    private String getCreateTempTab(String tempTable)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("CREATE TABLE ");
        sql.append(tempTable);
        sql.append(" (FIndex int, ");
        sql.append("FId VARCHAR(44),");
        sql.append("FMaterialNum NVARCHAR(80), ");
        sql.append("FMaterialName NVARCHAR(255),  ");
        sql.append("FMaterialModel NVARCHAR(80), ");
        sql.append("FIssueStorageName NVARCHAR(255), ");
        sql.append("FReceiptStorageName NVARCHAR(255), ");
        sql.append("FBaseUnitName NVARCHAR(255), ");
        sql.append("FStatUnitName NVARCHAR(255), ");
        sql.append("FOriginalQty NUMERIC(28,10),  ");
        sql.append("FAllocateQty NUMERIC(28,10) DEFAULT 0,  ");
        sql.append("FIssueBaseQty NUMERIC(28,10),  ");
        sql.append("FReceiptBaseQty NUMERIC(28,10), ");
        sql.append("FOnWayBaseQty NUMERIC(28,10), ");
        sql.append("FDamageBaseQty NUMERIC(28,10), ");
        sql.append("FIssueAmt NUMERIC(28,10), ");
        sql.append("FExportAmt NUMERIC(28,10) DEFAULT 0, ");
        sql.append("FImportAmt NUMERIC(28,10) DEFAULT 0, ");
        sql.append("FOnWayAmt NUMERIC(28,10) DEFAULT 0, ");
        sql.append("FDamageAmt NUMERIC(28,10), ");
        sql.append("FMaterialID VARCHAR(44), ");
        sql.append("FIssueOrgID VARCHAR(44), ");
        sql.append("FReceiptOrgID VARCHAR(44),");
        sql.append("FQtyPrecision int,");
        sql.append("FLevel int,");
        sql.append("FIsLeaf int,");
        sql.append("FParentID VARCHAR(44))");
        return sql.toString();
    }

    private String getUpdateNullSql(String tempTable)
    {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE ");
        sql.append(tempTable);
        sql.append(" SET FReceiptOrgID = NULL,FBaseUnitName = NULL,FStatUnitName= NULL");
        sql.append(" WHERE FINDEX = 2");
        return sql.toString();
    }

    private void executeSQL(Context ctx, String sql)
        throws BOSException
    {
        if(sql != null && sql.trim().length() > 0)
            executeUpdate(sql, null, ctx);
    }

    private void createOrgTempTable(String orgTempTable, RptParams params, Context ctx)
        throws BOSException
    {
        StringBuffer sql = new StringBuffer();
        sql.append("CREATE TABLE ");
        sql.append(orgTempTable);
        sql.append("(FID VARCHAR(44),");
        sql.append("FName NVARCHAR(255),");
        sql.append("FParentID VARCHAR(44),");
        sql.append("FLevel int,");
        sql.append("FIsLeaf int)");
        executeSQL(ctx, sql.toString());
        Object orgs[] = (Object[])params.getObject("storageOrg");
        FullOrgUnitInfo org = null;
        StringBuffer sql1 = new StringBuffer();
        sql1.append("insert into " + orgTempTable + "(");
        sql1.append("FID,FName,FParentID,FLevel,FIsLeaf) ");
        sql1.append("Values('");
        int level = 1;
        for(int i = 0; i < orgs.length; i++)
        {
            org = (FullOrgUnitInfo)orgs[i];
            sql = new StringBuffer();
            sql.append(sql1.toString());
            sql.append(org.getId().toString());
            sql.append("','");
            sql.append(org.getName());
            sql.append("','");
            if(org.getParent() != null)
                sql.append(org.getParent().getId().toString());
            sql.append("',");
            if(org.getLevel() > level)
                level = org.getLevel();
            sql.append(org.getLevel());
            if(org.isIsLeaf())
                sql.append(",1)");
            else
                sql.append(",0)");
            executeSQL(ctx, sql.toString());
        }

        params.setInt("MAXLEVEL", level);
    }

    private void executeTotal(String tempTable, String tempOrgTable, Context ctx, RptParams params)
        throws BOSException
    {
        int level = params.getInt("MAXLEVEL");
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO ");
        sql.append(tempTable);
        sql.append(" SELECT ");
        sql.append("1 AS FIndex,newbosid('EEEEEEEE'),A.FMaterialNum,A.FMaterialName,");
        sql.append("A.FMaterialModel,B.FName,");
        sql.append("A.FReceiptStorageName");
        sql.append(",A.FBaseUnitName,A.FStatUnitName,SUM(A.FOriginalQty),SUM(A.FAllocateQty),SUM(A.FIssueBaseQty),SUM(A.FReceiptBaseQty),");
        sql.append("SUM(A.FOnWayBaseQty),SUM(A.FDamageBaseQty),SUM(A.FIssueAmt),SUM(A.FExportAmt),SUM(A.FImportAmt),");
        sql.append("SUM(A.FOnWayAmt),SUM(A.FDamageAmt),");
        sql.append("A.FMaterialID,B.FID,FReceiptOrgID,A.FQtyPrecision,B.FLevel,B.FIsLeaf,B.FParentID");
        sql.append(" FROM ");
        sql.append(tempTable);
        sql.append(" AS A INNER JOIN ");
        sql.append(tempOrgTable);
        sql.append(" AS B ON A.FParentID = B.FID ");
        for(int i = level - 1; i > 0; i--)
        {
            StringBuffer exeSql = new StringBuffer();
            exeSql.append(sql.toString());
            exeSql.append("WHERE B.FLevel = ");
            exeSql.append(i);
            exeSql.append(" GROUP BY A.FMaterialNum,A.FMaterialName,B.FID,A.FMaterialModel,B.FNAME,A.FQtyPrecision,A.FParentID,B.FLevel,");
            exeSql.append("B.FIsLeaf,B.FParentID,A.FBaseUnitName,A.FStatUnitName,A.FReceiptStorageName,FReceiptOrgID,FMaterialID");
            executeSQL(ctx, exeSql.toString());
        }

        sql = new StringBuffer();
        sql.append("INSERT INTO ");
        sql.append(tempTable);
        sql.append(" SELECT ");
        sql.append("2 AS FIndex,newbosid('EEEEEEEE'), FMaterialNum,FMaterialName,");
        sql.append("FMaterialModel,FIssueStorageName,'");
        sql.append(SCMUtils.getResource("com.kingdee.eas.scm.im.inv.WarehsManageResource", "Total", ctx));
        sql.append("','111','111',SUM(FOriginalQty),SUM(FAllocateQty),SUM(FIssueBaseQty),SUM(FReceiptBaseQty),");
        sql.append("SUM(FOnWayBaseQty),SUM(FDamageBaseQty),SUM(FIssueAmt),SUM(FExportAmt),SUM(FImportAmt),SUM(FOnWayAmt),SUM(FDamageAmt),");
        sql.append("FMaterialID,FIssueOrgID,'111',FQtyPrecision,FLevel,1,FParentID");
        sql.append(" FROM ");
        sql.append(tempTable);
        sql.append(" GROUP BY FMaterialNum,FMaterialName,FMaterialID,FIssueOrgID,FMaterialModel,FIssueStorageName,FQtyPrecision,FParentID,FLevel ");
        executeSQL(ctx, sql.toString());
    }

    private List _getMaterialGroupLongNumber(Context ctx, IObjectValue materialTypeFrom, IObjectValue materialTypeTo, String materialStanderd)
        throws BOSException
    {
        boolean matFromIsLeaf = false;
        boolean matToIsLeaf = false;
        String from = null;
        String to = null;
        if(materialTypeFrom != null)
        {
            from = ((MaterialGroupInfo)materialTypeFrom).getNumber();
            matFromIsLeaf = ((MaterialGroupInfo)materialTypeFrom).isIsLeaf();
        }
        if(materialTypeTo != null)
        {
            to = ((MaterialGroupInfo)materialTypeTo).getNumber();
            matToIsLeaf = ((MaterialGroupInfo)materialTypeTo).isIsLeaf();
        }
        if(materialTypeFrom != null || materialTypeTo != null)
        {
            StringBuffer sql2 = new StringBuffer();
            sql2.append("select flongnumber from t_bd_materialGroup as mt  inner join T_BD_MaterialGroupDetial as mgd  on mt.fid = mgd.FMaterialGroupID where mgd.FMaterialGroupStandardID='");
            sql2.append(materialStanderd).append("'");
            if(materialTypeFrom != null)
                sql2.append(" and mt.fnumber >= '").append(from).append("' ");
            if(materialTypeTo != null)
                sql2.append(" and mt.fnumber <= '").append(to).append("' ");
            if((materialTypeFrom != null || materialTypeTo != null) && (!matFromIsLeaf || !matToIsLeaf))
                sql2.append(" and mt.fparentid is null");
            List list = new ArrayList();
            try
            {
                java.sql.Connection conn = EJBFactory.getConnection(ctx);
                RptRowSet rs = DBUtil.executeQuery(sql2.toString(), null, 0, -1, conn);
                int i = 0;
                for(; rs.next(); list.add(rs.getString("flongnumber")))
                    i++;

                return list;
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }
        return null;
    }

    private String getInsertSqlForStockTransferBill(String tempTable, String tempOrgTable, RptParams params, Context ctx)
        throws BOSException
    {
        FilterInfo filter = new FilterInfo();
        FilterItemCollection items = filter.getFilterItems();
        items.add(new FilterItemInfo("STOCKTRANSFERBILL.FBizDate", params.getObject("dateFrom"), CompareType.GREATER_EQUALS));
        StringBuffer sbFormula = new StringBuffer();
        int itemCount = 0;
        itemCount = _buildFilterStr(sbFormula, itemCount);
        items.add(new FilterItemInfo("STOCKTRANSFERBILL.FBizDate", params.getObject("dateTo"), CompareType.LESS_EQUALS));
        itemCount = _buildFilterStr(sbFormula, itemCount);
        String temp = null;
        temp = params.getString("materialFrom");
        if(temp != null)
        {
            items.add(new FilterItemInfo("MATERIAL.FNumber", temp, CompareType.GREATER_EQUALS));
            itemCount = _buildFilterStr(sbFormula, itemCount);
        }
        temp = params.getString("materialTo");
        if(temp != null)
        {
            items.add(new FilterItemInfo("MATERIAL.FNumber", temp, CompareType.LESS_EQUALS));
            itemCount = _buildFilterStr(sbFormula, itemCount);
        }
        IObjectValue from = (IObjectValue)params.getObject("matGrFrom");
        IObjectValue to = (IObjectValue)params.getObject("matGrTo");
        List materialGroup = null;
        try
        {
            materialGroup = _getMaterialGroupLongNumber(ctx, from, to, ((MaterialGroupStandardInfo)params.getObject("materialGroupStandard")).getId().toString());
        }
        catch(BOSException e)
        {
            e.printStackTrace();
        }
        if(materialGroup != null && materialGroup.size() != 0)
        {
            if(itemCount != 0)
                sbFormula.append(" and (");
            else
                sbFormula.append("(");
            for(int j = 0; j < materialGroup.size(); j++)
            {
                items.add(new FilterItemInfo("MATERIALGROUP.FLongNumber", materialGroup.get(j), CompareType.EQUALS));
                items.add(new FilterItemInfo("MATERIALGROUP.FLongNumber", materialGroup.get(j) + "!%", CompareType.LIKE));
                if(j == 0)
                    sbFormula.append("#" + itemCount++ + " or  #" + itemCount++ + "");
                else
                    sbFormula.append(" or #" + itemCount++ + " or  #" + itemCount++ + "");
            }

            sbFormula.append(")");
        } else
        if(from != null || to != null)
        {
            items.add(new FilterItemInfo("MATERIALGROUP.FLongNumber", "XXXXXXXXXXX", CompareType.EQUALS));
            if(itemCount == 0)
                sbFormula.append("#" + itemCount++);
            else
                sbFormula.append("  and  #" + itemCount++);
        }
        filter.setMaskString(sbFormula.toString());
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO ");
        sql.append(tempTable);
        sql.append(" SELECT ");
        sql.append("0 AS FIndex, newbosid('EEEEEEEE'),");
        sql.append("MATERIAL.FNumber AS materialNum, ");
        sql.append("MATERIAL.FName_");
        sql.append(getLoc(ctx));
        sql.append(" AS materialName, ");
        sql.append("MATERIAL.FModel AS materialModel, ");
        sql.append("ISSUESTORAGEORGUNIT.FName AS issueStorageName, ");
        sql.append("RECEIPTSTORAGEORGUNIT.FName_");
        sql.append(getLoc(ctx));
        sql.append(" AS receiptStorageName, ");
        sql.append("BASEUNIT.FName_");
        sql.append(getLoc(ctx));
        sql.append(" AS baseUnitName, ");
        sql.append(" '111' AS statUnitName, ");
        sql.append("0 AS OriginalQty, ");
        sql.append("SUM(ENTRY.Fqty) AS AllocateQty, ");
        sql.append("SUM(ENTRY.FIssueBaseQty) AS issueBaseQty, ");
        sql.append("SUM(ENTRY.FReceiptBaseQty) AS receiptBaseQty,");
        sql.append("SUM(CASE ENTRY.FBaseStatus WHEN 7 THEN 0 ");
        sql.append("ELSE ENTRY.FIssueBaseQty-ENTRY.FReceiptBaseQty ");
        sql.append("END) AS onWayBaseQty,");
        sql.append("SUM(CASE ENTRY.FBaseStatus WHEN 4 THEN 0 ");
        sql.append("ELSE ENTRY.FIssueBaseQty-ENTRY.FReceiptBaseQty END) AS damageBaseQty,");
        sql.append("SUM(decimal(ENTRY.FIssueQty*ENTRY.FPrice,21,8)*decimal(STOCKTRANSFERBILL.FExchangeRate,21,8)) AS issueAmt,");
        sql.append("SUM(CASE WHEN ISSUEBILLENTRY.FACTUALCOST IS NULL THEN 0 ELSE ISSUEBILLENTRY.FACTUALCOST END) AS ExportAmt, ");
        sql.append("SUM(CASE WHEN INHOUSEBILLENTRY.FACTUALCOST IS NULL THEN 0 ELSE INHOUSEBILLENTRY.FACTUALCOST END) AS ImportAmt, ");
        sql.append("SUM(0) AS OnWayAmt, ");
        sql.append("SUM(CASE ENTRY.FBaseStatus WHEN 4 THEN 0 ");
        sql.append("ELSE ");
        sql.append("decimal((ENTRY.FIssueQty-ENTRY.FReceiptQty) * ENTRY.FPrice,21,8) * decimal(STOCKTRANSFERBILL.FExchangeRate,21,8) ");
        sql.append("END) AS damageAmt ,");
        sql.append("MATERIAL.FID,ISSUESTORAGEORGUNIT.FID,RECEIPTSTORAGEORGUNIT.FID,MULUNIT.FQtyPrecision ");
        sql.append(",ISSUESTORAGEORGUNIT.FLevel,ISSUESTORAGEORGUNIT.FIsLeaf,ISSUESTORAGEORGUNIT.FParentID ");
        sql.append("FROM T_IM_StockTransferBill AS STOCKTRANSFERBILL ");
        sql.append("INNER JOIN T_IM_StockTransferBillEntry AS ENTRY ");
        sql.append("ON STOCKTRANSFERBILL.FID = ENTRY.FParentID ");
        sql.append("INNER JOIN ");
        sql.append(tempOrgTable);
        sql.append(" AS ISSUESTORAGEORGUNIT ");
        sql.append("ON STOCKTRANSFERBILL.FIssueStorageOrgUnitID = ISSUESTORAGEORGUNIT.FID ");
        sql.append("LEFT OUTER JOIN");
        sql.append("(SELECT TEMP_ISSUEBILLENTRY.FStockTransBillEntryID, SUM(TEMP_ISSUEBILLENTRY.FACTUALCOST) AS FACTUALCOST ");
        sql.append("FROM T_IM_MOVEISSUEBILL AS TEMP_ISSUEBILL INNER JOIN T_IM_MOVEISSUEBILLENTRY AS TEMP_ISSUEBILLENTRY ");
        sql.append("ON TEMP_ISSUEBILL.FID = TEMP_ISSUEBILLENTRY.FPARENTID WHERE TEMP_ISSUEBILL.FBaseStatus = 7 OR TEMP_ISSUEBILL.FBaseStatus = 4 ");
        sql.append("GROUP BY TEMP_ISSUEBILLENTRY.FStockTransBillEntryID) ");
        sql.append("AS ISSUEBILLENTRY ON ISSUEBILLENTRY.FStockTransBillEntryID = ENTRY.FID ");
        sql.append("LEFT OUTER JOIN");
        sql.append("(SELECT TEMP_INHOUSEBILLENTRY.FStockTransBillEntryID, ");
        sql.append("SUM(TEMP_INHOUSEBILLENTRY.FACTUALCOST) AS FACTUALCOST FROM T_IM_MOVEINWAREHSBILL AS TEMP_INHOUSEBILL ");
        sql.append("INNER JOIN T_IM_MOVEINWAREHSBILLENTRY AS TEMP_INHOUSEBILLENTRY ON TEMP_INHOUSEBILL.FID = TEMP_INHOUSEBILLENTRY.FPARENTID ");
        sql.append("WHERE TEMP_INHOUSEBILL.FBaseStatus = 7 OR TEMP_INHOUSEBILL.FBaseStatus = 4 GROUP BY TEMP_INHOUSEBILLENTRY.FStockTransBillEntryID) AS INHOUSEBILLENTRY ");
        sql.append("ON INHOUSEBILLENTRY.FStockTransBillEntryID=ENTRY.FID ");
        sql.append("LEFT OUTER JOIN T_ORG_Storage AS RECEIPTSTORAGEORGUNIT ");
        sql.append("ON STOCKTRANSFERBILL.FReceiptStorageOrgUnitID = RECEIPTSTORAGEORGUNIT.FID ");
        sql.append("LEFT OUTER JOIN T_BD_Material AS MATERIAL ");
        sql.append("ON ENTRY.FMaterialID = MATERIAL.FID ");
        sql.append("LEFT OUTER JOIN T_BD_MeasureUnit AS BASEUNIT ");
        sql.append("ON ENTRY.FBaseUnitID = BASEUNIT.FID ");
        sql.append("left join T_BD_MaterialGroupDetial as  MGD on MGD.FMaterialID = MATERIAL.fid  ");
        sql.append("left join T_BD_MaterialGroup as MATERIALGROUP on MATERIALGROUP.FID = MGD.FMaterialGroupID  ");
        sql.append("LEFT OUTER JOIN T_BD_MultiMeasureUnit AS MULUNIT");
        sql.append(" ON (ENTRY.FMaterialID = MULUNIT.FMaterialID AND ENTRY.FBaseUnitID = MULUNIT.FMeasureUnitID)");
        sql.append("WHERE  (STOCKTRANSFERBILL.FBaseStatus= 7 OR STOCKTRANSFERBILL.FBaseStatus= 4) ");
        sql.append("AND (ENTRY.FIssueQty > 0) AND ");
        sql.append(filter.toString());
        sql.append("GROUP BY ");
        sql.append("ISSUESTORAGEORGUNIT.FName,MATERIAL.FNumber,MATERIAL.FName_");
        sql.append(getLoc(ctx));
        sql.append(" ,MATERIAL.FModel,");
        sql.append("RECEIPTSTORAGEORGUNIT.FName_");
        sql.append(getLoc(ctx));
        sql.append(",BASEUNIT.FName_");
        sql.append(getLoc(ctx));
        sql.append(",");
        sql.append("ISSUESTORAGEORGUNIT.FID,RECEIPTSTORAGEORGUNIT.FID,MATERIAL.FID,MULUNIT.FQtyPrecision ");
        sql.append(",ISSUESTORAGEORGUNIT.FIsLeaf,ISSUESTORAGEORGUNIT.FLevel,ISSUESTORAGEORGUNIT.FParentID ");
        return sql.toString();
    }

    private String getInsertSqlForTransferOrderBill(String tempTable, String tempOrgTable, RptParams params, Context ctx)
        throws BOSException
    {
        FilterInfo filter = new FilterInfo();
        FilterItemCollection items = filter.getFilterItems();
        items.add(new FilterItemInfo("STOCKTRANSFERBILL.FBizDate", params.getObject("dateFrom"), CompareType.GREATER_EQUALS));
        StringBuffer sbFormula = new StringBuffer();
        int itemCount = 0;
        itemCount = _buildFilterStr(sbFormula, itemCount);
        items.add(new FilterItemInfo("STOCKTRANSFERBILL.FBizDate", params.getObject("dateTo"), CompareType.LESS_EQUALS));
        itemCount = _buildFilterStr(sbFormula, itemCount);
        String temp = null;
        temp = params.getString("materialFrom");
        if(temp != null)
        {
            items.add(new FilterItemInfo("MATERIAL.FNumber", temp, CompareType.GREATER_EQUALS));
            itemCount = _buildFilterStr(sbFormula, itemCount);
        }
        temp = params.getString("materialTo");
        if(temp != null)
        {
            items.add(new FilterItemInfo("MATERIAL.FNumber", temp, CompareType.LESS_EQUALS));
            itemCount = _buildFilterStr(sbFormula, itemCount);
        }
        IObjectValue from = (IObjectValue)params.getObject("matGrFrom");
        IObjectValue to = (IObjectValue)params.getObject("matGrTo");
        List materialGroup = null;
        try
        {
            materialGroup = _getMaterialGroupLongNumber(ctx, from, to, ((MaterialGroupStandardInfo)params.getObject("materialGroupStandard")).getId().toString());
        }
        catch(BOSException e)
        {
            e.printStackTrace();
        }
        if(materialGroup != null && materialGroup.size() != 0)
        {
            if(itemCount != 0)
                sbFormula.append(" and (");
            else
                sbFormula.append("(");
            for(int j = 0; j < materialGroup.size(); j++)
            {
                items.add(new FilterItemInfo("MATERIALGROUP.FLongNumber", materialGroup.get(j), CompareType.EQUALS));
                items.add(new FilterItemInfo("MATERIALGROUP.FLongNumber", materialGroup.get(j) + "!%", CompareType.LIKE));
                if(j == 0)
                    sbFormula.append("#" + itemCount++ + " or  #" + itemCount++ + "");
                else
                    sbFormula.append(" or #" + itemCount++ + " or  #" + itemCount++ + "");
            }

            sbFormula.append(")");
        } else
        if(from != null || to != null)
        {
            items.add(new FilterItemInfo("MATERIALGROUP.FLongNumber", "XXXXXXXXXXX", CompareType.EQUALS));
            if(itemCount == 0)
                sbFormula.append("#" + itemCount++);
            else
                sbFormula.append("  and  #" + itemCount++);
        }
        filter.setMaskString(sbFormula.toString());
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO ");
        sql.append(tempTable);
        sql.append(" SELECT ");
        sql.append("0 AS FIndex, newbosid('EEEEEEEE'),");
        sql.append("MATERIAL.FNumber AS materialNum, ");
        sql.append("MATERIAL.FName_");
        sql.append(getLoc(ctx));
        sql.append(" AS materialName, ");
        sql.append("MATERIAL.FModel AS materialModel, ");
        sql.append("ISSUESTORAGEORGUNIT.FName AS issueStorageName, ");
        sql.append("RECEIPTSTORAGEORGUNIT.FName_");
        sql.append(getLoc(ctx));
        sql.append(" AS receiptStorageName, ");
        sql.append("BASEUNIT.FName_");
        sql.append(getLoc(ctx));
        sql.append(" AS baseUnitName, ");
        sql.append(" '111' AS statUnitName, ");
        sql.append("SUM(ENTRY.CFOrgqty) AS OriginalQty, ");
        sql.append("SUM(ENTRY.Fqty) AS AllocateQty, ");
        sql.append("SUM(ENTRY.FIssueBaseQty) AS issueBaseQty, ");
        sql.append("SUM(ENTRY.FReceiptBaseQty) AS receiptBaseQty,");
        sql.append("SUM(CASE ENTRY.FBaseStatus WHEN 7 THEN 0 ");
        sql.append("ELSE ENTRY.FIssueBaseQty-ENTRY.FReceiptBaseQty ");
        sql.append("END) AS onWayBaseQty,");
        sql.append("SUM(CASE ENTRY.FBaseStatus WHEN 4 THEN 0 ");
        sql.append("ELSE ENTRY.FIssueBaseQty-ENTRY.FReceiptBaseQty END) AS damageBaseQty,");
        sql.append("SUM(decimal(ENTRY.FIssueQty*ENTRY.FPrice,21,8)*decimal(STOCKTRANSFERBILL.FExchangeRate,21,8)) AS issueAmt,");
        sql.append("SUM(CASE WHEN ISSUEBILLENTRY.FACTUALCOST IS NULL THEN 0 ELSE ISSUEBILLENTRY.FACTUALCOST END) AS ExportAmt, ");
        sql.append("SUM(CASE WHEN INHOUSEBILLENTRY.FACTUALCOST IS NULL THEN 0 ELSE INHOUSEBILLENTRY.FACTUALCOST END) AS ImportAmt, ");
        sql.append("SUM(0) AS OnWayAmt, ");
        sql.append("SUM(CASE ENTRY.FBaseStatus WHEN 4 THEN 0 ");
        sql.append("ELSE ");
        sql.append("decimal((ENTRY.FIssueQty-ENTRY.FReceiptQty) * ENTRY.FPrice,21,8) * decimal(STOCKTRANSFERBILL.FExchangeRate,21,8) ");
        sql.append("END) AS damageAmt ,");
        sql.append("MATERIAL.FID,ISSUESTORAGEORGUNIT.FID,RECEIPTSTORAGEORGUNIT.FID,MULUNIT.FQtyPrecision ");
        sql.append(",ISSUESTORAGEORGUNIT.FLevel,ISSUESTORAGEORGUNIT.FIsLeaf,ISSUESTORAGEORGUNIT.FParentID ");
        sql.append("FROM T_IM_TransferOrderBill AS STOCKTRANSFERBILL ");
        sql.append("INNER JOIN T_IM_TransferOrderBillEntry AS ENTRY ");
        sql.append("ON STOCKTRANSFERBILL.FID = ENTRY.FParentID ");
        sql.append("INNER JOIN ");
        sql.append(tempOrgTable);
        sql.append(" AS ISSUESTORAGEORGUNIT ");
        sql.append("ON ENTRY.FIssueStorageOrgUnitID = ISSUESTORAGEORGUNIT.FID ");
        sql.append("LEFT OUTER JOIN");
        sql.append("(SELECT TEMP_ISSUEBILLENTRY.FSaleOrderEntryID, TEMP_ISSUEBILLENTRY.FSTORAGEORGUNITID AS FStorageOrgUnitID, SUM(TEMP_ISSUEBILLENTRY.FACTUALCOST) AS FACTUALCOST ");
        sql.append("FROM T_IM_SaleIssueBill AS TEMP_ISSUEBILL INNER JOIN T_IM_SaleIssueEntry AS TEMP_ISSUEBILLENTRY ");
        sql.append("ON TEMP_ISSUEBILL.FID = TEMP_ISSUEBILLENTRY.FPARENTID WHERE TEMP_ISSUEBILL.FBaseStatus = 7 OR TEMP_ISSUEBILL.FBaseStatus = 4 ");
        sql.append("GROUP BY TEMP_ISSUEBILLENTRY.FSaleOrderEntryID, TEMP_ISSUEBILLENTRY.FSTORAGEORGUNITID) ");
        sql.append("AS ISSUEBILLENTRY ON ISSUEBILLENTRY.FSaleOrderEntryID = ENTRY.FID AND ISSUEBILLENTRY.FStorageOrgUnitID = ENTRY.FIssueStorageOrgUnitID ");
        sql.append("LEFT OUTER JOIN");
        sql.append("(SELECT TEMP_INHOUSEBILLENTRY.FPurOrderEntryID,TEMP_INHOUSEBILLENTRY.FStorageOrgUnitID AS FStorageOrgUnitID, ");
        sql.append("SUM(TEMP_INHOUSEBILLENTRY.FACTUALCOST) AS FACTUALCOST FROM T_IM_PurInWarehsBill AS TEMP_INHOUSEBILL ");
        sql.append("INNER JOIN T_IM_PurInWarehsEntry AS TEMP_INHOUSEBILLENTRY ON TEMP_INHOUSEBILL.FID = TEMP_INHOUSEBILLENTRY.FPARENTID ");
        sql.append("WHERE TEMP_INHOUSEBILL.FBaseStatus = 7 OR TEMP_INHOUSEBILL.FBaseStatus = 4 GROUP BY TEMP_INHOUSEBILLENTRY.FPurOrderEntryID,TEMP_INHOUSEBILLENTRY.FStorageOrgUnitID) AS INHOUSEBILLENTRY ");
        sql.append("ON INHOUSEBILLENTRY.FPurOrderEntryID = ENTRY.FID AND INHOUSEBILLENTRY.FStorageOrgUnitID = ENTRY.FReceiveStorageOrgUnitID ");
        sql.append("LEFT OUTER JOIN T_ORG_Storage AS RECEIPTSTORAGEORGUNIT ");
        sql.append("ON ENTRY.FReceiveStorageOrgUnitID = RECEIPTSTORAGEORGUNIT.FID ");
        sql.append("LEFT OUTER JOIN T_BD_Material AS MATERIAL ");
        sql.append("ON ENTRY.FMaterialID = MATERIAL.FID ");
        sql.append("LEFT OUTER JOIN T_BD_MeasureUnit AS BASEUNIT ");
        sql.append("ON ENTRY.FBaseUnitID = BASEUNIT.FID ");
        sql.append("left join T_BD_MaterialGroupDetial as  MGD on MGD.FMaterialID = MATERIAL.fid  ");
        sql.append("left join T_BD_MaterialGroup as MATERIALGROUP on MATERIALGROUP.FID = MGD.FMaterialGroupID  ");
        sql.append("LEFT OUTER JOIN T_BD_MultiMeasureUnit AS MULUNIT");
        sql.append(" ON (ENTRY.FMaterialID = MULUNIT.FMaterialID AND ENTRY.FBaseUnitID = MULUNIT.FMeasureUnitID)");
        sql.append("WHERE  (STOCKTRANSFERBILL.FBaseStatus= 7 OR STOCKTRANSFERBILL.FBaseStatus= 4) ");
        sql.append("AND (ENTRY.FIssueQty > 0) AND ");
        sql.append(filter.toString());
        sql.append("GROUP BY ");
        sql.append("ISSUESTORAGEORGUNIT.FName,MATERIAL.FNumber,MATERIAL.FName_");
        sql.append(getLoc(ctx));
        sql.append(" ,MATERIAL.FModel,");
        sql.append("RECEIPTSTORAGEORGUNIT.FName_");
        sql.append(getLoc(ctx));
        sql.append(",BASEUNIT.FName_");
        sql.append(getLoc(ctx));
        sql.append(",");
        sql.append("ISSUESTORAGEORGUNIT.FID,RECEIPTSTORAGEORGUNIT.FID,MATERIAL.FID,MULUNIT.FQtyPrecision ");
        sql.append(",ISSUESTORAGEORGUNIT.FIsLeaf,ISSUESTORAGEORGUNIT.FLevel,ISSUESTORAGEORGUNIT.FParentID ");
        return sql.toString();
    }

    private int _buildFilterStr(StringBuffer filterStr, int itemCount)
    {
        if(itemCount == 0)
            filterStr.append("#");
        else
            filterStr.append(" and #");
        filterStr.append(itemCount++);
        return itemCount;
    }

    protected RptParams _init(Context ctx, RptParams params)
        throws BOSException, EASBizException
    {
        return params;
    }

    protected RptParams _query(Context ctx, RptParams params, int from, int len)
        throws BOSException, EASBizException
    {
        Boolean ckBxShowMShortName = (Boolean)params.getObject("ckBxShowMShortName");
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT temp.FMaterialNum, temp.FMaterialName, material.FShortName, temp.FMaterialModel, temp.FIssueStorageName, temp.FReceiptStorageName,");
        sql.append("temp.FBaseUnitName,temp.FStatUnitName,isnull(temp.FOriginalQty,0) as FOriginalQty,isnull(temp.FAllocateQty,0) as FAllocateQty,isnull(temp.FIssueBaseQty,0) as FIssueBaseQty,isnull(temp.FReceiptBaseQty,0) as FReceiptBaseQty,isnull(temp.FOnWayBaseQty,0) as FOnWayBaseQty,isnull(temp.FDamageBaseQty,0) as FDamageBaseQty,");
        sql.append("isnull(temp.FIssueAmt,0) as FIssueAmt,isnull(temp.FExportAmt,0) as FExportAmt,isnull(temp.FImportAmt,0) as FImportAmt,isnull(temp.FExportAmt,0)-isnull(temp.FImportAmt,0) AS FOnWayAmt,isnull(temp.FDamageAmt,0) as FDamageAmt,temp.FIndex, temp.FMaterialID,temp.FIssueOrgID,temp.FReceiptOrgID ,isnull(temp.FQtyPrecision,0) as FQtyPrecision,");
        sql.append("temp.FLevel,temp.FIsLeaf,temp.FIssueStorageName AS FIssueStorageName2 ");
        sql.append("FROM ");
        sql.append(params.getString("tempTable"));
        sql.append(" AS temp ").append(" LEFT OUTER JOIN T_BD_Material as material on temp.FMaterialNum = material.FNumber ");
        sql.append(" WHERE FLEVEL = 1");
        if(ckBxShowMShortName != null && ckBxShowMShortName.booleanValue())
        {
            String txtMShortNameFrom = (String)params.getObject("shortNameFrom");
            String txtMShortNameTo = (String)params.getObject("shortNameTo");
            if(txtMShortNameFrom == null || txtMShortNameFrom.trim().length() == 0)
                txtMShortNameFrom = null;
            if(txtMShortNameTo == null || txtMShortNameTo.trim().length() == 0)
                txtMShortNameTo = null;
            if(txtMShortNameFrom != null && txtMShortNameFrom.trim().length() > 0)
            {
                if(txtMShortNameFrom.indexOf("'") != -1)
                    txtMShortNameFrom = txtMShortNameFrom.replaceAll("'", "''");
                sql.append(" and material.FShortName >= '").append(txtMShortNameFrom.trim()).append("' ");
            }
            if(txtMShortNameFrom == null && txtMShortNameTo != null && txtMShortNameTo.trim().length() > 0)
            {
                if(txtMShortNameTo.indexOf("'") != -1)
                    txtMShortNameTo = txtMShortNameTo.replaceAll("'", "''");
                sql.append(" and ( material.FShortName <= '").append(txtMShortNameTo.trim()).append("' or material.FShortName is null ) ");
            }
            if(txtMShortNameFrom != null && txtMShortNameTo != null && txtMShortNameTo.trim().length() > 0)
            {
                if(txtMShortNameTo.indexOf("'") != -1)
                    txtMShortNameTo = txtMShortNameTo.replaceAll("'", "''");
                sql.append(" and material.FShortName <= '").append(txtMShortNameTo.trim()).append("' ");
            }
        }
        sql.append(" Order By FMaterialNum, FIssueStorageName,FIndex, FReceiptStorageName Desc");
        RptRowSet rs = executeQuery(sql.toString(), null, from, len, ctx);
        RptParams pp = new RptParams();
        pp.setObject("rowset", rs);
        return pp;
    }

    protected IRowSet _getIdList(Context ctx, RptParams param)
        throws BOSException
    {
        String rsList = "select  fid from " + param.getString("tempTable") + " where flevel=1 Order By FMaterialNum, FIssueStorageName, FIndex";
        return DbUtil.executeQuery(ctx, rsList);
    }

    protected RptParams _getDataForIdList(Context ctx, List idList, RptParams params)
        throws BOSException
    {
        Boolean ckBxShowMShortName = (Boolean)params.getObject("ckBxShowMShortName");
        StringBuffer fid = (new StringBuffer("temp.FId IN (  '")).append(idList.get(0).toString()).append("'");
        int i = 1;
        for(int n = idList.size(); i < n; i++)
            fid.append(", '").append(idList.get(i).toString()).append("'");

        fid.append(" ) ");
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT temp.FMaterialNum,temp.FMaterialName,material.FShortName, temp.FMaterialModel, temp.FIssueStorageName, temp.FReceiptStorageName,");
        sql.append("temp.FBaseUnitName,temp.FStatUnitName,temp.FAllocateQty,temp.FIssueBaseQty,temp.FReceiptBaseQty,temp.FOnWayBaseQty,temp.FDamageBaseQty,");
        sql.append("temp.FIssueAmt,temp.FExportAmt,temp.FImportAmt,temp.FExportAmt-temp.FImportAmt AS FOnWayAmt,temp.FDamageAmt,temp.FIndex, temp.FMaterialID,temp.FIssueOrgID,temp.FReceiptOrgID ,temp.FQtyPrecision,");
        sql.append("temp.FLevel,temp.FIsLeaf,temp.FIssueStorageName AS FIssueStorageName2 ");
        sql.append("FROM ");
        sql.append(params.getString("tempTable"));
        sql.append(" AS temp ").append(" LEFT OUTER JOIN T_BD_Material as material on temp.FMaterialNum = material.FNumber ");
        sql.append(" where ").append(fid.toString());
        if(ckBxShowMShortName != null && ckBxShowMShortName.booleanValue())
        {
            String txtMShortNameFrom = (String)params.getObject("shortNameFrom");
            String txtMShortNameTo = (String)params.getObject("shortNameTo");
            if(txtMShortNameFrom == null || txtMShortNameFrom.trim().length() == 0)
                txtMShortNameFrom = null;
            if(txtMShortNameTo == null || txtMShortNameTo.trim().length() == 0)
                txtMShortNameTo = null;
            if(txtMShortNameFrom != null && txtMShortNameFrom.trim().length() > 0)
            {
                if(txtMShortNameFrom.indexOf("'") != -1)
                    txtMShortNameFrom = txtMShortNameFrom.replaceAll("'", "''");
                sql.append(" and material.FShortName >= '").append(txtMShortNameFrom.trim()).append("' ");
            }
            if(txtMShortNameFrom == null && txtMShortNameTo != null && txtMShortNameTo.trim().length() > 0)
            {
                if(txtMShortNameTo.indexOf("'") != -1)
                    txtMShortNameTo = txtMShortNameTo.replaceAll("'", "''");
                sql.append(" and ( material.FShortName <= '").append(txtMShortNameTo.trim()).append("' or material.FShortName is null ) ");
            }
            if(txtMShortNameFrom != null && txtMShortNameTo != null && txtMShortNameTo.trim().length() > 0)
            {
                if(txtMShortNameTo.indexOf("'") != -1)
                    txtMShortNameTo = txtMShortNameTo.replaceAll("'", "''");
                sql.append(" and material.FShortName <= '").append(txtMShortNameTo.trim()).append("' ");
            }
        }
        sql.append(" Order By temp.FMaterialNum, temp.FIssueStorageName, temp.FIndex");
        RptRowSet rs = executeQuery(sql.toString(), null, ctx);
        RptParams pp = new RptParams();
        pp.setObject("rowset", rs);
        return pp;
    }

    private void updateStatUnit(Context ctx, String tempTable, RptParams params)
        throws BOSException
    {
        StringBuffer sql = new StringBuffer();
        sql.append("update ").append(tempTable).append(" set (FStatUnitName,").append("FIssueBaseQty,FReceiptBaseQty,FOnWayBaseQty,FDamageBaseQty ").append(") = (select m.fname_").append(getLoc(ctx) + " ,").append("round(decimal(FIssueBaseQty,21,8)/decimal(mm.FBaseConvsRate,21,8),mm.FQtyPrecision),").append("round(decimal(FReceiptBaseQty,21,8)/decimal(mm.FBaseConvsRate,21,8),mm.FQtyPrecision),").append("round(decimal(FOnWayBaseQty,21,8)/decimal(mm.FBaseConvsRate,21,8),mm.FQtyPrecision),").append("round(decimal(FDamageBaseQty,21,8)/decimal(mm.FBaseConvsRate,21,8),mm.FQtyPrecision) ").append(" from t_bd_multimeasureunit mm,t_bd_measureunit  m ").append("where ").append(tempTable).append(".fmaterialid = mm.fmaterialid and mm.fisstatunit = 1 and mm.fmeasureunitid=m.fid").append(")");
        executeSQL(ctx, sql.toString());
    }

    private static Logger logger = Logger.getLogger("com.kingdee.eas.scm.im.rpt.app.StockTransferRptFacadeControllerBean");

}